package com.vegetable.modules.service.bank.convert;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.vegetable.modules.entity.bank.vo.PSBCTran;
import com.vegetable.modules.entity.finance.Transactions;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 邮储流水标准化字段输出
 */
@Slf4j
public class PSBCConverter extends BankGenerator {
	private static final String bankMark = "PSBC";

	public List<Transactions> convert(MultipartFile file) throws Exception {
		// 尝试使用现有的处理逻辑
		List<Transactions> result = convertUsingEasyPOI(file);
		if (CollectionUtil.isNotEmpty(result)) {
			return result;
		}
		
		// 如果现有逻辑无法处理，则使用新的处理分支
		return convertNewFormat(file);
	}

	private List<Transactions> convertUsingEasyPOI(MultipartFile file) throws Exception {
		List<Transactions> targetJson = new ArrayList<>();
		try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
			Sheet sheet = workbook.getSheetAt(0);
			Row headRow = sheet.getRow(5);
			Short cellNum = headRow.getLastCellNum();
			Field[] fields = PSBCTran.class.getDeclaredFields();
			List<Field> cFields = Arrays.stream(fields).filter(el -> el.getAnnotation(Excel.class) != null).collect(Collectors.toList());
			for(int i = 0; i < cellNum; i ++){
				String cellName = headRow.getCell(i).getStringCellValue();
				String fieldName = cFields.get(i).getAnnotation(Excel.class).name();
				if(StrUtil.equals(cellName, fieldName)){
					log.info("格式是对的.");
				}else{
					throw new RuntimeException("格式不对了！！！");
				}
			}
		}catch (Exception e){
			e.printStackTrace();
		}

		ImportParams importParams = new ImportParams();
		importParams.setNeedVerify(true);
		importParams.setHeadRows(5);
		List<PSBCTran> orderList = ExcelImportUtil.importExcel(file.getInputStream(), PSBCTran.class, importParams);

		if(CollectionUtil.isNotEmpty(orderList)){
			for(PSBCTran payTran : orderList){
				if(ObjectUtil.isNull(payTran.getOutAmount()) || ObjectUtil.isNull(payTran.getInAmount())){
					continue;
				}
				Transactions tmp = new Transactions();
				BeanUtils.copyProperties(payTran, tmp);
				tmp.setTradingType(NumberUtil.isGreater(payTran.getOutAmount(), BigDecimal.ZERO) ? "EXPENSE" : "INCOME");
				tmp.setTradingAmount("INCOME".equals(tmp.getTradingType()) ? payTran.getInAmount() : payTran.getOutAmount());
				tmp.setStatus("SUCCESS");
				tmp.setRemark(payTran.getPostScript());
				tmp.setTradingDescription(payTran.getSummary());
				// 1. 解析日期部分 (yyyyMMdd)
				LocalDate date = payTran.getTranDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
				// 2. 解析时间部分 (HH:mm:ss)
				LocalTime time = payTran.getTranTime().toInstant().atZone(ZoneId.systemDefault()).toLocalTime();
				LocalDateTime dateTime = LocalDateTime.of(date, time);
				Date legacyDate = Date.from(dateTime.atZone(ZoneId.systemDefault()).toInstant());
				tmp.setTradingTime(legacyDate);
				tmp.setChannel(bankMark);
				tmp.setTradingNo(Transactions.generateUniqueFlowId(tmp));
				targetJson.add(tmp);
			}
		}
		return targetJson;
	}

	/**
	 * 处理新的邮储银行流水格式
	 * @param file 银行流水文件
	 * @return 交易记录列表
	 * @throws Exception 处理异常
	 */
	private List<Transactions> convertNewFormat(MultipartFile file) throws Exception {
		List<Transactions> result = new ArrayList<>();
		try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
			Sheet sheet = workbook.getSheetAt(0);
			int lastRow = sheet.getLastRowNum();

			// 跳过前几行的表头信息，从实际数据行开始处理
			// 根据示例数据，从第4行开始是实际的交易数据（0-based索引）
			for (int i = 3; i <= lastRow; i++) {
				Row currentR = sheet.getRow(i);
				if (currentR == null) {
					continue;
				}

				// 检查是否是数据行（通过判断是否有交易时间）
				String tranTimeStr = getStringCell(currentR, 0);
				if (StrUtil.isBlank(tranTimeStr)) {
					continue;
				}

				try {
					Transactions transaction = new Transactions();
					
					// 解析交易时间（交易时间在第0列）
					// 格式为：2025-11-11 11:35
					transaction.setTradingTime(DateUtil.parse(tranTimeStr, "yyyy-MM-dd HH:mm"));

					// 交易金额（第5列）
					BigDecimal amountStr = getBCCell(currentR, 5);
					// 清理金额字符串，移除非数字字符（保留负号）
					transaction.setTradingAmount(amountStr.abs()); // 存储绝对值
					transaction.setTradingType(amountStr.compareTo(BigDecimal.ZERO) >= 0 ? "INCOME" : "EXPENSE");

					// 付款户名（第1列）
					transaction.setBankName("INCOME".equals(transaction.getTradingType()) ? getStringCell(currentR,1) : getStringCell(currentR,2));


					// 收款户名（第2列）
					transaction.setCounterparty("INCOME".equals(transaction.getTradingType()) ? getStringCell(currentR,1) : getStringCell(currentR,2));
					
					// 对方账号（第3列）
					transaction.setBankNo(getStringCell(currentR, 3));
					
					// 币种（第4列）
					// 忽略，系统中默认为人民币
					
					// 余额（第6列）
					transaction.setBalanceAmount(getBCCell(currentR, 6));
					
					// 摘要（第7列）
					transaction.setTradingDescription(getStringCell(currentR, 7));
					
					// 备注（第8列）
					transaction.setRemark(getStringCell(currentR, 8));
					
					// 设置其他必要字段
					transaction.setStatus("SUCCESS");
					transaction.setChannel(bankMark);
					transaction.setTradingNo(Transactions.generateUniqueFlowId(transaction));
					
					result.add(transaction);
				} catch (Exception e) {
					log.error("处理邮储银行流水第{}行时发生异常", i + 1, e);
				}
			}
		} catch (Exception e) {
			log.error("转换邮储银行流水发生异常", e);
			throw e;
		}
		
		return result;
	}
}